﻿Imports System.Data.SqlClient
Imports System.IO

Public Class frmStaffProfilesEdit
    Private ImageUrl As String = ""

    Sub FillBranches()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim Branches As ArrayList = GetBranches()

            For Each Branch As String In Branches
                Me.CombBranch.Items.Add(Branch)
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub FillPositionsList()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select * From Position", cnn)
            Dim Reader As SqlDataReader

            Me.CombJobTitle.Items.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.CombJobTitle.Items.Add(Reader.Item("Position"))
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub FillNationalityList()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select * From Nationality", cnn)
            Dim Reader As SqlDataReader

            Me.CombNationalityEn.Items.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.CombNationalityEn.Items.Add(Reader.Item("NationalityEn"))
            End While
            cnn.Close()

            Me.CombNationalityEn.SelectedItem = "Saudi"

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub FillReligionList()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select * From Religion", cnn)
            Dim Reader As SqlDataReader

            Me.combReligionEn.Items.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.combReligionEn.Items.Add(Reader.Item("ReligionEn"))
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub bindphoto(ByVal imagedata As Byte(), ByVal pbox As PictureBox)

        Dim ms As MemoryStream
        ms = New MemoryStream(imagedata, 0, imagedata.Length)
        ms.Write(imagedata, 0, imagedata.Length)
        Dim im As Image
        im = Image.FromStream(ms, True)
        pbox.Image = im

    End Sub

    Sub FillEmployeeDetails()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select * From StaffProfiles where EmpNo=" & Me.txtEEmpNo.Text.Trim, cnn)
            Dim reader As SqlDataReader

            cnn.Open()
            reader = cmd.ExecuteReader
            While reader.Read
                Me.txtNameEn.Text = reader.Item("FullNameEn")
                Me.txtNameAr.Text = reader.Item("FullNameAr")
                Me.ComboMaritalStatus.Text = reader.Item("MaritalStatus")
                Me.CombNationalityEn.Text = reader.Item("Nationality")
                Me.combReligionEn.Text = reader.Item("ReligionEn")
                Me.CombGenderEn.Text = reader.Item("Gender")
                Me.DTPBirth.Value = CDate(reader.Item("DateofBirth"))
                Me.txtMobile.Text = reader.Item("Mobile")
                Me.txtEmail.Text = reader.Item("Email")
                Me.txtAddress.Text = reader.Item("Address")
                Me.txtGOSINo.Text = reader.Item("GOSINo")
                Me.txtSponsor.Text = reader.Item("Sponsor")

                Me.txtPassportNo.Text = reader.Item("PassportNo")
                Me.txtPssprtIsuPlce.Text = reader.Item("PassIssuePlace")
                Me.txtPssprtIsuPlceAr.Text = reader.Item("PassIssuePlaceAr")
                Me.DTPPasspIssueDate.Value = CDate(reader.Item("PassIssueDate"))
                Me.DTPPasspExpireDate.Value = CDate(reader.Item("ExDate"))

                Me.txtIDNo.Text = reader.Item("IDNo")
                Me.txtIDIssuePlace.Text = reader.Item("IDIssuePlace")
                Me.txtIDIssuePlaceAr.Text = reader.Item("IDIssuePlaceAr")
                Me.DTPIDIssueDate.Value = CDate(reader.Item("IDIssueDate"))
                Me.DTPIDExpireDate.Value = CDate(reader.Item("IDExDate"))

                Me.CombJobTitle.Text = reader.Item("Position")
                Me.DTPHireDate.Value = CDate(reader.Item("HireDate"))
                Me.nudVacationEntilement.Value = reader.Item("VacationPeriod")
                Me.CombBranch.Text = reader.Item("Branch")

                Me.txtDoctorName.Text = reader.Item("DoctorName")
                Me.txtDoctorAddress.Text = reader.Item("DoctorAddress")
                Me.txtDoctorPhoneNo.Text = reader.Item("DoctorPhoneNo")
                Me.CombBloodType.SelectedItem = reader.Item("BloodType")
                Me.txtMedicalConditions.Text = reader.Item("MedicalConditions")
                Me.txtAllergies.Text = reader.Item("Allergies")
                Me.txtCurrentMedications.Text = reader.Item("CurrentMedications")

                Me.txtEmergContactName.Text = reader.Item("EmergContactName")
                Me.txtEmergContactRelationship.Text = reader.Item("EmergContactRelationship")
                Me.txtEmergContactAddress.Text = reader.Item("EmergContactAddress")
                Me.txtEmergContactPhone.Text = reader.Item("EmergContactPhone")

                Me.txtBS.Value = reader.Item("SalaryBS")
                Me.txtHA.Value = reader.Item("SalaryHA")
                Me.txtTA.Value = reader.Item("SalaryTA")
                Me.txtFuel.Value = reader.Item("SalaryFuel")
                Me.txtSupervision.Value = reader.Item("SalarySupervision")
                Me.txtTuitionFess.Value = reader.Item("SalaryTuitionFees")
                Me.txtMobileAllowance.Value = reader.Item("SalaryMobile")
                Me.txtOthers.Value = reader.Item("Others")
                Me.txtTotalSalary.Text = CDbl(reader.Item("TotalSalary")).ToString("N2")

                If IsDBNull(reader.Item("Photo")) Then
                    Me.PictureBox1.Image = My.Resources.default_profile
                Else
                    Me.bindphoto(reader.Item("Photo"), Me.PictureBox1)
                End If

                'enabling the remove_photo button
                Me.btnRmovePhoto.Enabled = True
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
         Me.ErrProv.Clear()
        If Me.txtNameEn.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtNameEn, "Please fill in all details")
            Me.txtNameEn.Focus()

        ElseIf Me.txtNameAr.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtNameAr, "Please fill in all details")
            Me.txtNameAr.Focus()

        ElseIf Me.ComboMaritalStatus.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.ComboMaritalStatus, "Please fill in all details")
            Me.ComboMaritalStatus.Focus()

        ElseIf Me.CombNationalityEn.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.CombNationalityEn, "Please fill in all details")
            Me.CombNationalityEn.Focus()

        ElseIf Me.combReligionEn.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.combReligionEn, "Please fill in all details")
            Me.combReligionEn.Focus()

        ElseIf Me.CombGenderEn.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.CombGenderEn, "Please fill in all details")
            Me.CombGenderEn.Focus()

        ElseIf Me.txtMobile.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtMobile, "Please fill in all details")
            Me.txtMobile.Focus()

        ElseIf Me.txtAddress.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtAddress, "Please fill in all details")
            Me.txtAddress.Focus()

        ElseIf Me.txtGOSINo.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtGOSINo, "Please fill in all details")
            Me.txtGOSINo.Focus()

        ElseIf Me.txtSponsor.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtSponsor, "Please fill in all details")
            Me.txtSponsor.Focus()

        ElseIf Me.txtPassportNo.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtPassportNo, "Please fill in all details")
            Me.txtPassportNo.Focus()

        ElseIf Me.txtPssprtIsuPlce.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtPssprtIsuPlce, "Please fill in all details")
            Me.txtPssprtIsuPlce.Focus()

        ElseIf Me.txtPssprtIsuPlceAr.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtPssprtIsuPlceAr, "Please fill in all details")
            Me.txtPssprtIsuPlceAr.Focus()

        ElseIf Me.txtIDNo.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtIDNo, "Please fill in all details")
            Me.txtIDNo.Focus()

        ElseIf Me.txtIDIssuePlace.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtIDIssuePlace, "Please fill in all details")
            Me.txtIDIssuePlace.Focus()

        ElseIf Me.txtIDIssuePlaceAr.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtIDIssuePlaceAr, "Please fill in all details")
            Me.txtIDIssuePlaceAr.Focus()

        ElseIf Me.CombJobTitle.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.CombJobTitle, "Please fill in all details")
            Me.CombJobTitle.Focus()

        ElseIf Me.CombBranch.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.CombBranch, "Please fill in all details")
            Me.CombBranch.Focus()

        ElseIf Not Me.txtEmail.Text.Trim.Length = 0 And _
            Not System.Text.RegularExpressions.Regex.IsMatch(Me.txtEmail.Text.Trim, "^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$") Then
            MsgBox("Please enter a valid email address or leave blank")
            Exit Sub
        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim Photo As String

                If ImageUrl <> "" Then
                    Photo = " ,Photo=@Photo "
                Else
                    Photo = ""
                End If

                Dim cmd As New SqlCommand("Update StaffProfiles Set FullNameAr=@FullNameAr,FullNameEn=@FullNameEn," & _
                                          "MaritalStatus=@MaritalStatus,Nationality=@Nationality,ReligionEn=@ReligionEn,Gender=@Gender," & _
                                          "DateofBirth=@DateofBirth,Mobile=@Mobile,Email=@Email,Address=@Address,GOSINo=@GOSINo,Sponsor=@Sponsor," & _
                                          "PassportNo=@PassportNo,PassIssuePlace=@PassIssuePlace,PassIssuePlaceAr=@PassIssuePlaceAr," & _
                                          "PassIssueDate=@PassIssueDate,ExDate=@ExDate," & _
                                          "IDNo=@IDNo,IDIssuePlace=@IDIssuePlace,IDIssuePlaceAr=@IDIssuePlaceAr,IDIssueDate=@IDIssueDate," & _
                                          "IDExDate=@IDExDate,Position=@Position,HireDate=@HireDate," & _
                                          "VacationPeriod=@VacationPeriod,Branch=@Branch," & _
                                          "DoctorName=@DoctorName,DoctorAddress=@DoctorAddress,DoctorPhoneNo=@DoctorPhoneNo,BloodType=@BloodType,MedicalConditions=@MedicalConditions," & _
                                          "Allergies=@Allergies,CurrentMedications=@CurrentMedications," & _
                                          "EmergContactName=@EmergContactName,EmergContactRelationship=@EmergContactRelationship,EmergContactAddress=@EmergContactAddress," & _
                                          "EmergContactPhone=@EmergContactPhone," & _
                                          "SalaryBS=@SalaryBS,SalaryHA=@SalaryHA,SalaryTA=@SalaryTA,SalaryMobile=@SalaryMobile," & _
                                          "Others=@Others,TotalSalary=@TotalSalary " & Photo & " Where EmpNo=" & CInt(Me.txtEEmpNo.Text.Trim), cnn)

                cmd.Parameters.Clear()
                cmd.Parameters.AddWithValue("@FullNameAr", Me.txtNameAr.Text.Trim)
                cmd.Parameters.AddWithValue("@FullNameEn", Me.txtNameEn.Text.Trim)
                cmd.Parameters.AddWithValue("@MaritalStatus", Me.ComboMaritalStatus.Text)
                cmd.Parameters.AddWithValue("@Nationality", Me.CombNationalityEn.Text)
                cmd.Parameters.AddWithValue("@ReligionEn", Me.combReligionEn.Text)
                cmd.Parameters.AddWithValue("@Gender", Me.CombGenderEn.Text)
                cmd.Parameters.AddWithValue("@DateofBirth", Me.DTPBirth.Value)
                cmd.Parameters.AddWithValue("@Mobile", Me.txtMobile.Text.Trim)
                cmd.Parameters.AddWithValue("@Email", Me.txtEmail.Text.Trim)
                cmd.Parameters.AddWithValue("@Address", Me.txtAddress.Text.Trim)
                cmd.Parameters.AddWithValue("@GOSINo", Me.txtGOSINo.Text.Trim)
                cmd.Parameters.AddWithValue("@Sponsor", Me.txtSponsor.Text.Trim)

                cmd.Parameters.AddWithValue("@PassportNo", Me.txtPassportNo.Text.Trim)
                cmd.Parameters.AddWithValue("@PassIssuePlace", Me.txtPssprtIsuPlce.Text.Trim)
                cmd.Parameters.AddWithValue("@PassIssuePlaceAr", Me.txtPssprtIsuPlceAr.Text.Trim)
                cmd.Parameters.AddWithValue("@PassIssueDate", Me.DTPPasspIssueDate.Value)
                cmd.Parameters.AddWithValue("@ExDate", Me.DTPPasspExpireDate.Value)

                cmd.Parameters.AddWithValue("@IDNo", Me.txtIDNo.Text.Trim)
                cmd.Parameters.AddWithValue("@IDIssuePlace", Me.txtIDIssuePlace.Text.Trim)
                cmd.Parameters.AddWithValue("@IDIssuePlaceAr", Me.txtIDIssuePlaceAr.Text.Trim)
                cmd.Parameters.AddWithValue("@IDIssueDate", Me.DTPIDIssueDate.Value)
                cmd.Parameters.AddWithValue("@IDExDate", Me.DTPIDExpireDate.Value)

                cmd.Parameters.AddWithValue("@Position", Me.CombJobTitle.Text)
                cmd.Parameters.AddWithValue("@HireDate", Me.DTPHireDate.Value)
                cmd.Parameters.AddWithValue("@VacationPeriod", nudVacationEntilement.Value)
                cmd.Parameters.AddWithValue("@Branch", Me.CombBranch.Text)

                cmd.Parameters.AddWithValue("@DoctorName", Me.txtDoctorName.Text.Trim)
                cmd.Parameters.AddWithValue("@DoctorAddress", Me.txtDoctorAddress.Text.Trim)
                cmd.Parameters.AddWithValue("@DoctorPhoneNo", Me.txtDoctorPhoneNo.Text.Trim)
                cmd.Parameters.AddWithValue("@BloodType", Me.CombBloodType.Text.Trim)
                cmd.Parameters.AddWithValue("@MedicalConditions", Me.txtMedicalConditions.Text.Trim)
                cmd.Parameters.AddWithValue("@Allergies", Me.txtAllergies.Text.Trim)
                cmd.Parameters.AddWithValue("@CurrentMedications", Me.txtCurrentMedications.Text.Trim)

                cmd.Parameters.AddWithValue("@EmergContactName", Me.txtEmergContactName.Text)
                cmd.Parameters.AddWithValue("@EmergContactRelationship", Me.txtEmergContactRelationship.Text.Trim)
                cmd.Parameters.AddWithValue("@EmergContactAddress", Me.txtEmergContactAddress.Text.Trim)
                cmd.Parameters.AddWithValue("@EmergContactPhone", Me.txtEmergContactPhone.Text.Trim)

                cmd.Parameters.AddWithValue("@SalaryBS", Me.txtBS.Value)
                cmd.Parameters.AddWithValue("@SalaryHA", Me.txtHA.Value)
                cmd.Parameters.AddWithValue("@SalaryTA", Me.txtTA.Value)
                cmd.Parameters.AddWithValue("@SalaryFuel", Me.txtFuel.Value)
                cmd.Parameters.AddWithValue("@SalarySupervision", Me.txtSupervision.Value)
                cmd.Parameters.AddWithValue("@SalaryTuitionFees", Me.txtTuitionFess.Value)
                cmd.Parameters.AddWithValue("@SalaryMobile", Me.txtMobileAllowance.Value)
                cmd.Parameters.AddWithValue("@Others", Me.txtOthers.Value)
                cmd.Parameters.AddWithValue("@TotalSalary", CDbl(Me.txtTotalSalary.Text.Trim))

                If ImageUrl <> "" Then
                    cmd.Parameters.AddWithValue("@Photo", binarize(ImageUrl))
                    ImageUrl = ""
                End If

                cnn.Open()
                cmd.ExecuteNonQuery()
                cnn.Close()

                Me.Cursor = Cursors.Default

                MsgBox("Updated Successfully")

                PrintStaffProfile(CInt(Me.txtEEmpNo.Text))

                Me.Close()

            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub choose_photo()
        Dim opf As New OpenFileDialog
        opf.Filter = "Image File (*.jpg,*.bmp,*.png)|*.jpg;*.bmp;*.png|All files (*.*)|*.*"
        opf.FilterIndex = 1
        opf.Title = "Choose employee photo"
        If opf.ShowDialog() <> Windows.Forms.DialogResult.Cancel Then
            ImageUrl = opf.FileName
            PictureBox1.Image = Image.FromFile(ImageUrl)
        End If
    End Sub

    Private Function binarize(ByVal purl As String) As Byte()
        Dim fstream As FileStream
        Dim imgdata As Byte()
        Dim data As Byte()
        Dim finfo As FileInfo
        finfo = New FileInfo(purl)
        Dim numbyte As Long
        Dim br As BinaryReader
        numbyte = finfo.Length
        fstream = New FileStream(purl, FileMode.Open, FileAccess.Read)
        br = New BinaryReader(fstream)
        data = br.ReadBytes(numbyte)
        imgdata = data
        Return imgdata
    End Function

    Private Sub ButClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButClose.Click
        Me.Close()
    End Sub

    Private Sub CalcSalary()
        Me.txtTotalSalary.Text = CDbl(Me.txtBS.Value + Me.txtHA.Value + Me.txtMobileAllowance.Value + Me.txtTA.Value + Me.txtOthers.Value).ToString("N2")
    End Sub

    Private Sub nudBS_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtBS.ValueChanged
        CalcSalary()
    End Sub

    Private Sub nudHA_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtHA.ValueChanged
        CalcSalary()
    End Sub

    Private Sub nudTA_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtTA.ValueChanged
        CalcSalary()
    End Sub

    Private Sub nudMobile_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtMobileAllowance.ValueChanged
        CalcSalary()
    End Sub

    Private Sub nudTeamManagement_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtOthers.ValueChanged
        CalcSalary()
    End Sub

    Private Sub btnBrows_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBrows.Click
        choose_photo()
    End Sub

    Private Sub btnRmovePhoto_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRmovePhoto.Click
        Try
            Me.Cursor = Cursors.WaitCursor
            Dim cmd As New SqlCommand("update StaffProfiles set Photo=null where EmpNo=" & CInt(Me.txtEEmpNo.Text.Trim), cnn1)
            cnn1.Open()
            cmd.ExecuteNonQuery()
            PictureBox1.Image = My.Resources.default_profile
            ImageUrl = ""
            MsgBox("Image Removed successfuly!")

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            If cnn1.State = ConnectionState.Open Then
                cnn1.Close()
            End If
            Me.Cursor = Cursors.Default
        End Try
    End Sub
End Class